package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.common.exception.AppException;
import com.sq.partner.manager.dao.IGatewaySubChannelDao;
import com.sq.partner.manager.mybatis.model.GatewaySubChannel;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.TradInsuPlanGroup;
import com.sq.partner.manager.mybatis.model.TreeDto;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

@Repository
public class GatewaySubChannelDaoImpl extends BaseDaoImpl implements
		IGatewaySubChannelDao {
	private static Logger logger = LogUtil.getLog(GatewaySubChannelDaoImpl.class);
	@Override
	public Page<GatewaySubChannel> querySubChannelByFuzzyCriteria(
			Integer pageIndex, GatewaySubChannel subChannel, GatewayUser user)
			throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		Page subChannelPage = null;
		try {
			String deptStr = CoreDeptDaoImpl.transDeptList2deptCodeStr(user
					.getDepts());

			StringBuilder sql = new StringBuilder();
			//noted By gzz@20150513 for del unnecessary be related
			/*String sqlbase = "SELECT K.SUB_CHANNEL_CODE,K.SUB_CHANNEL_NAME, NVL(K.PARENT_CHANNEL_CODE, 'root') AS PARENT_CHANNEL_CODE,        NVL(M.SUB_CHANNEL_NAME, '顶级机构') AS PARENT_CHANNEL_NAME, K.CHANNEL_INFO_CODE, K.CHANNEL_INFO_NAME, K.DEPT_CODE  FROM T_GATEWAY_SUB_CHANNEL M  RIGHT JOIN (SELECT N.SUB_CHANNEL_CODE,"
					+ "N.SUB_CHANNEL_NAME,  N.PARENT_CHANNEL_CODE,  N.FK_CHANNEL_CODE AS CHANNEL_INFO_CODE, L.CHANNEL_NAME AS CHANNEL_INFO_NAME, L.DEPT_CODE  AS DEPT_CODE  FROM T_GATEWAY_SUB_CHANNEL N INNER JOIN T_GATEWAY_CHANNEL_INFO L ON N.FK_CHANNEL_CODE = L.CHANNEL_CODE  WHERE N.STATUS = '1' START WITH N.PARENT_CHANNEL_CODE IS NULL   CONNECT BY PRIOR N.SUB_CHANNEL_CODE = N.PARENT_CHANNEL_CODE AND N.FK_CHANNEL_CODE IN (SELECT M.CHANNEL_CODE FROM T_GATEWAY_CHANNEL_INFO M  WHERE M.STATUS = '1' AND M.DEPT_CODE IN"
					+ "(SELECT P.DEPT_CODE  FROM T_CORE_DEPT P  WHERE LENGTH(P.DEPT_CODE) = 2     CONNECT BY PRIOR P.DEPT_CODE = P.SNR_DEPT    START WITH P.DEPT_CODE IN ('"
					+ deptStr
					+ "')))) K  ON M.SUB_CHANNEL_CODE = K.PARENT_CHANNEL_CODE where 1=1 ";*/
			String sqlbase = "SELECT K.SUB_CHANNEL_CODE,K.SUB_CHANNEL_NAME,K.PARENT_CHANNEL_CODE AS PARENT_CHANNEL_CODE,     "
					+ "   NVL(M.SUB_CHANNEL_NAME, '顶级机构') AS PARENT_CHANNEL_NAME, K.CHANNEL_INFO_CODE, K.CHANNEL_INFO_NAME, "
					+ "K.DEPT_CODE,OPERATE_TYPE"
					+ "  FROM T_GATEWAY_SUB_CHANNEL M  RIGHT JOIN (SELECT N.SUB_CHANNEL_CODE,"
					+ "N.SUB_CHANNEL_NAME,  N.PARENT_CHANNEL_CODE,  N.FK_CHANNEL_CODE AS CHANNEL_INFO_CODE, "
					+ "L.CHANNEL_NAME AS CHANNEL_INFO_NAME, L.DEPT_CODE AS DEPT_CODE,"
					+ "CASE WHEN N.BRANCH IS NULL THEN"
					+ " '<a href='''||'javascript:void(0)'||''' onClick='''||'updateSubChannel(this);return false'''||'>编辑</a> <a href='''||'javascript:void(0)'''||' onClick='''||'deleteSubChannel(this);return false'''||'>删除</a>'"
					+ " END OPERATE_TYPE, n.oper_time"
					+ " FROM T_GATEWAY_SUB_CHANNEL N INNER JOIN T_GATEWAY_CHANNEL_INFO L "
					+ "ON N.FK_CHANNEL_CODE = L.CHANNEL_CODE ";
			if(!user.isHQFlag())
			{
				sqlbase=sqlbase+"and  L.DEPT_CODE IN ('"+ deptStr+ "') ";
			}
			sqlbase=sqlbase+"  WHERE N.STATUS = '1' START WITH N.PARENT_CHANNEL_CODE IS NULL"
					+ "  CONNECT BY PRIOR N.SUB_CHANNEL_CODE = N.PARENT_CHANNEL_CODE "
					+ " ) K  ON M.SUB_CHANNEL_CODE = K.PARENT_CHANNEL_CODE where 1=1 ";

			sql.append(sqlbase);

			List params = new ArrayList();

			if (StrUtil.isNotEmpty(subChannel.getSubChannelCode())) {
				String subChannelCode = subChannel.getSubChannelCode();
				String subChannelCodeStr = "%" + subChannelCode + "%";
				sql.append(" and  K.sub_channel_code like ? ");
				params.add(subChannelCodeStr);
			}

			if (StrUtil.isNotEmpty(subChannel.getSubChannelName())) {
				String subChannelName = subChannel.getSubChannelName();
				String subChannelNameStr = "%" + subChannelName + "%";
				sql.append(" and K.sub_channel_name like ? ");
				params.add(subChannelNameStr);
			}
			sql.append(" order by k.oper_time desc,k.sub_channel_code desc ");
			conn = this.getConnection();

			Long total = 0L;
			pstmt = conn.prepareStatement("select count(*) from ("
					+ sql.toString() + ")");

			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(i + 1, params.get(i));
			}

			rs = pstmt.executeQuery();
			if (rs.next()) {
				total = rs.getLong(1);
			}

			subChannelPage = new Page(pageIndex, total);

			SqlUtil sqlUtil = new SqlUtil();

			String sqlPage = sqlUtil.buildPageSqlForOracle(sql.toString(),
					subChannelPage).toString();
			logger.debug("执行SQL："+sqlPage);
			pstmt1 = conn.prepareStatement(sqlPage);
			for (int i = 0; i < params.size(); i++) {
				pstmt1.setObject(i + 1, params.get(i));
			}

			rs = pstmt1.executeQuery();

			List transList = DBUtil.transRs2List(rs);
			subChannelPage.setData(transList);

		} finally {
			close(pstmt);
			close(pstmt1);
			close(rs);
			close(conn);
		}

		return subChannelPage;
	}

	@Override
	public Page<GatewaySubChannel> queryChannelTreeByOneUser(GatewayUser user, Page<GatewaySubChannel> page)
			throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try {
			StringBuilder sql = new StringBuilder();
			String deptStr = CoreDeptDaoImpl.transDeptList2deptCodeStr(user.getDepts());
			//noted By gzz@20150513 for del unnecessary be related
			/*String sql = "SELECT N.SUB_CHANNEL_CODE, N.SUB_CHANNEL_NAME, N.PARENT_CHANNEL_CODE FROM T_GATEWAY_SUB_CHANNEL N WHERE N.STATUS = '1'  START WITH N.PARENT_CHANNEL_CODE IS NULL CONNECT BY PRIOR N.SUB_CHANNEL_CODE = N.PARENT_CHANNEL_CODE  "
					+ "AND N.FK_CHANNEL_CODE IN (SELECT M.CHANNEL_CODE    FROM T_GATEWAY_CHANNEL_INFO M   WHERE M.STATUS = '1'     AND M.DEPT_CODE IN (SELECT P.DEPT_CODE FROM T_CORE_DEPT P  WHERE LENGTH(P.DEPT_CODE) = 2 CONNECT BY PRIOR P.DEPT_CODE = P.SNR_DEPT START "
					+ "WITH P.DEPT_CODE IN ('" + deptStr + "')))";*/
			sql.append(" SELECT N.SUB_CHANNEL_CODE, N.SUB_CHANNEL_NAME, N.PARENT_CHANNEL_CODE,L.CHANNEL_NAME FROM T_GATEWAY_SUB_CHANNEL N INNER JOIN T_GATEWAY_CHANNEL_INFO L ON N.FK_CHANNEL_CODE = L.CHANNEL_CODE");
			if(!user.isHQFlag()){
				sql.append(" and  L.DEPT_CODE IN ('"+ deptStr+ "') ");
			}
			sql.append(" WHERE N.STATUS = '1' ");
			List<String> params = new ArrayList<String>();
			if (StrUtil.isNotEmpty(page.getType().getSubChannelCode())) {
				String subChannelCodeStr = "%" + page.getType().getSubChannelCode() + "%";
				sql.append(" and  n.sub_channel_code like ? ");
				params.add(subChannelCodeStr);
			}

			if (StrUtil.isNotEmpty(page.getType().getSubChannelName())) {
				String subChannelNameStr = "%" + page.getType().getSubChannelName() + "%";
				sql.append(" and n.sub_channel_name like ? ");
				params.add(subChannelNameStr);
			}
			sql.append(" START WITH N.PARENT_CHANNEL_CODE IS NULL CONNECT BY PRIOR N.SUB_CHANNEL_CODE = N.PARENT_CHANNEL_CODE");
			conn = this.getConnection();
			Long total = 0L;
			pstmt = conn.prepareStatement("select count(*) from ("+ sql.toString() + ")");
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(i + 1, params.get(i));
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				total = rs.getLong(1);
			}
			page.setTotal(total);
			SqlUtil sqlUtil = new SqlUtil();
			String sqlPage = sqlUtil.buildPageSqlForOracle(sql.toString(),page).toString();
			logger.debug("执行SQL："+sqlPage);
			pstmt1 = conn.prepareStatement(sqlPage);
			for (int i = 0; i < params.size(); i++) {
				pstmt1.setObject(i + 1, params.get(i));
			}
			rs = pstmt1.executeQuery();
			List subAllChannelInfo = DBUtil.transRs2List(rs);
			page.setData(subAllChannelInfo);
		} finally {
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public boolean save(GatewaySubChannel subChannel) throws Exception {
		return this.add("insertGatewaySubChannel", subChannel);
	}

	@Override
	public boolean delete(String subChannelCode) throws Exception {
		return this.delete("delGatewaySubChannelById", subChannelCode);
	}

	@Override
	public List<GatewaySubChannel> queryBySubChannel(
			GatewaySubChannel subChannel) throws Exception {

		return this.queryList("queryGatewaySubChannelByMap", subChannel);
	}

	@Override
	public List<TreeDto> querySubChannelTreeByParentChannelCode(String parentChannelCode) throws Exception{
		return queryList("querySubChannelTreeByParentChannelCode", parentChannelCode);
	}
	
	@Override
	public List<GatewaySubChannel> querySubChannelByParentCode(String parentCode)
			throws Exception {
		// TODO Auto-generated method stub
		return this.queryList("queryGatewaySubChannelByParentCode", parentCode);
	}

	@Override
	public void delDefaultSubChannel(String subChannelCode) throws Exception {
		// TODO Auto-generated method stub
		this.delete("delGatewaySubChannelBySubCode", subChannelCode);
	}

	@Override
	public GatewaySubChannel querySubChannelInfoById(String subChannelCode)
			throws Exception {
		// TODO Auto-generated method stub
		return (GatewaySubChannel) this.query("queryGatewaySubChannelById",
				subChannelCode);
	}

	@Override
	public Page<GatewaySubChannel> queryPageForGatewaySubChannelInfo(Page<GatewaySubChannel> page) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			GatewaySubChannel subGatewayChannelInfo =page.getType();
			sqlbuf.append("SELECT L.SUB_CHANNEL_NAME AS SUBCHANNELNAME,L.SUB_CHANNEL_CODE AS SUBCHANNELCODE FROM T_GATEWAY_SUB_CHANNEL L WHERE L.STATUS = '1' ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			
			if(StrUtil.isNotEmpty(subGatewayChannelInfo.getSubChannelCode())){
				sqlbuf.append(" and l.SUB_CHANNEL_CODE like ?");
				islike.add(true);
				list.add(subGatewayChannelInfo.getSubChannelCode());
			}
			if(StrUtil.isNotEmpty(subGatewayChannelInfo.getSubChannelName())){
				sqlbuf.append(" and l.SUB_CHANNEL_NAME like ?");
				islike.add(true);
				list.add(subGatewayChannelInfo.getSubChannelName());
			}
			sqlbuf.append(" START WITH L.PARENT_CHANNEL_CODE = '"+subGatewayChannelInfo.getParentChannelCode()+"' CONNECT BY NOCYCLE PRIOR L.SUB_CHANNEL_CODE = L.PARENT_CHANNEL_CODE order by l.SUB_CHANNEL_CODE ");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);

			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		
		return page;
	}

	@Override
	public void addBatchMtSubBranchInfo(List<GatewaySubChannel> list) throws AppException {
		if(null!=list && list.size()>0){
			try {
				this.add("insertBatchMtSubBranchInfoList", list);
			} catch (Exception e) {
				logger.info("数据库应用忙",e);
				throw new AppException("数据库应用忙，请稍后重试！");
			}
		}
	}

	@Override
	public GatewaySubChannel querySubChannelInfoByCondition(
			Map<String, String> map) throws Exception {
		return (GatewaySubChannel) this.query("queryGatewaySubChannelByCondition",map);
	}

	@Override
	public boolean delete1(Map<String, String> map) throws Exception {
		// TODO Auto-generated method stub
		return  this.delete("delGatewaySubChannelById1", map);
	}

	@Override
	/**
	 * 产品导出销售网点
	 * @param map
	 * @return
	 * @throws Exception
	 */
	public  List<Map<String, Object>> impNlifeSub(Map<String, Object>map) throws Exception{
		// TODO Auto-generated method stub
		return this.queryList("impNlifeSubMap", map);
	}

	@Override
	public List<GatewaySubChannel> searchGatewaySubChannelsListByplanCode(
			Map<String, Object> maps) throws Exception {
		// TODO Auto-generated method stub
		return this.queryList("searchGatewaySubChannelsListByplanCode", maps);
	}
}
